/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package MembuatLaporan;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 *
 * @author Ignacio
 */
//untuk method-method yang akan digunakan
public class Laporan {

    private String tanggal;
    private String noTransBeli;
    private String noTransJual;
    private long hargaBeli;
    private long hargaJual;
    private long totalBeli;
    private long totalJual;
    private long stokPulsa;
    private long pemasukan;
    private Connection connect;
    private static Laporan lapor;
    private String tanggalAwal;
    private String tanggalAkhir;
    long sumPulsa=0;

    public Laporan(Connection konek) {
        this.connect = konek;
    }

    public Laporan() {
    }

    public static Laporan getKoneksiKontrol() throws SQLException, FileNotFoundException, IOException {
        if (lapor == null) {
            lapor = new Laporan(Koneksi.koneksi.getDBConnection());
        }
        return lapor;

    }

    /**
     * @return the tanggal
     */
    public String getTanggal() {
        return tanggal;
    }

    /**
     * @param tanggal the tanggal to set
     */
    public void setTanggal(String tanggal) {
        this.tanggal = tanggal;
    }

    /**
     * @return the noTransBeli
     */
    public String getNoTransBeli() {
        return noTransBeli;
    }

    /**
     * @param noTransBeli the noTransBeli to set
     */
    public void setNoTransBeli(String noTransBeli) {
        this.noTransBeli = noTransBeli;
    }

    /**
     * @return the noTransJual
     */
    public String getNoTransJual() {
        return noTransJual;
    }

    /**
     * @param noTransJual the noTransJual to set
     */
    public void setNoTransJual(String noTransJual) {
        this.noTransJual = noTransJual;
    }

    /**
     * @return the hargaBeli
     */
    public long getHargaBeli() {
        return hargaBeli;
    }

    /**
     * @param hargaBeli the hargaBeli to set
     */
    public void setHargaBeli(long hargaBeli) {
        this.hargaBeli = hargaBeli;
    }

    /**
     * @return the hargaJual
     */
    public long getHargaJual() {
        return hargaJual;
    }

    /**
     * @param hargaJual the hargaJual to set
     */
    public void setHargaJual(long hargaJual) {
        this.hargaJual = hargaJual;
    }

    /**
     * @return the totalBeli
     */
    public long getTotalBeli() {
        return totalBeli;
    }

    /**
     * @param totalBeli the totalBeli to set
     */
    public void setTotalBeli(long totalBeli) {
        this.totalBeli = totalBeli;
    }

    /**
     * @return the totalJual
     */
    public long getTotalJual() {
        return totalJual;
    }

    /**
     * @param totalJual the totalJual to set
     */
    public void setTotalJual(long totalJual) {
        this.totalJual = totalJual;
    }

    /**
     * @return the stokPulsa
     */
    public long getStokPulsa() {
        return stokPulsa;
    }

    /**
     * @param stokPulsa the stokPulsa to set
     */
    public void setStokPulsa(long stokPulsa) {
        this.stokPulsa = stokPulsa;
    }

    public void getAllDataBeli(String tglAwal, String tglAkhir,List<Laporan> laporans) throws SQLException {
//        getAllDataJual(tglAwal, tglAkhir);
        sumPulsa=0;
        PreparedStatement statement = null;
        ResultSet result = null;
        connect.setAutoCommit(false);
        String sql = "select waktu,notransaksipembelian,jumlahpembelian,hargapembelian "
                + "from pembelianpulsa where waktu between '" + tglAwal + "'"
                + " and '" + tglAkhir + "'  and statustutupbuku= 0";

        statement = connect.prepareStatement(sql);
        result = statement.executeQuery();


        while (result.next()) {
            Laporan trans = new Laporan();
            String waktu = result.getString("waktu");
            String noBeli = result.getString("notransaksipembelian");
            long jumBeli = result.getLong("jumlahpembelian");
            long beliHarga = result.getLong("hargapembelian");
            String noJual = "-";
            long nominal = 0;
            long jualHarga = 0;
            sumPulsa+=jumBeli;
            trans.setTanggal(waktu);
            trans.setNoTransBeli(noBeli);
            trans.setTotalBeli(jumBeli);
            trans.setHargaBeli(beliHarga);
            trans.setNoTransJual(noJual);
            trans.setTotalJual(nominal);
            trans.setHargaJual(jualHarga);
            trans.setStokPulsa(sumPulsa);
            laporans.add(trans);
        }


//        return laporans;
    }

    public List<Laporan> getAllDataJual(String tglAwal, String tglAkhir) throws SQLException {

        List<Laporan> laporans = new ArrayList<Laporan>();
        getAllDataBeli(tglAwal, tglAkhir,laporans);
        PreparedStatement statement = null;
        ResultSet result = null;
        connect.setAutoCommit(false);
        String sql = "select waktu,notransaksipenjualan,nominal,hargapenjualan "
                + "from penjualanpulsa where waktu between '" + tglAwal
                + "' and '" + tglAkhir + "' and statustutupbuku= 0";
        statement = connect.prepareStatement(sql);
        result = statement.executeQuery();

        while (result.next()) {
            Laporan trans = new Laporan();
            String waktu = result.getString("waktu");
            String noBeli = "-";
            long jumBeli = 0;
            long beliHarga = 0;
            String noJual = result.getString("notransaksipenjualan");
            long nominal = result.getLong("nominal");
            long jualHarga = result.getLong("hargapenjualan");
            sumPulsa-=nominal;
            trans.setTanggal(waktu);
            trans.setNoTransBeli(noBeli);
            trans.setTotalBeli(jumBeli);
            trans.setHargaBeli(beliHarga);
            trans.setNoTransJual(noJual);
            trans.setTotalJual(nominal);
            trans.setHargaJual(jualHarga);
            trans.setStokPulsa(sumPulsa);
            laporans.add(trans);
        }
        return laporans;
    }

    public void ubahStatusPemb(String notrans) throws SQLException {
        PreparedStatement state = null;
        String sql = "update pembelianpulsa set statustutupbuku=1 where notransaksipembelian=(?)";
        state = connect.prepareStatement(sql);
        state.setString(1, notrans);
        state.execute();
        state.close();
        connect.commit();
    }

    public void ubahStatusPenj(String notrans) throws SQLException {
        PreparedStatement state = null;
        String sql = "update penjualanpulsa set statustutupbuku=1 where notransaksipenjualan=(?)";
        state = connect.prepareStatement(sql);
        state.setString(1, notrans);
        state.execute();
        state.close();
        connect.commit();
    }

    /**
     * @return the tanggalAwal
     */
    public String getTanggalAwal() {
        return tanggalAwal;
    }

    /**
     * @param tanggalAwal the tanggalAwal to set
     */
    public void setTanggalAwal(String tanggalAwal) throws Exception {
        if (tanggalAwal.isEmpty()||tanggalAwal==null||tanggalAwal.equals("")) {
            throw new Exception("Pilih tanggal awal terlebih dahulu");
        } else {
            this.tanggalAwal = tanggalAwal;
        }
    }

    /**
     * @return the tanggalAkhir
     */
    public String getTanggalAkhir() {
        return tanggalAkhir;
    }

    /**
     * @param tanggalAkhir the tanggalAkhir to set
     */
    public void setTanggalAkhir(String tanggalAkhir) throws Exception {
        if (tanggalAkhir.isEmpty()||tanggalAkhir==null||tanggalAkhir.equals("")) {
            throw new Exception("Pilih tanggal akhir terlebih dahulu");
        } else {
            this.tanggalAkhir = tanggalAkhir;
        }
    }
}

